package com.util;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Properties;

import net.sf.json.JSONObject;

import com.caigen.sql.CaigenConnectionPoolDataSource;

public class DBFUtil {
	private static String prex = File.pathSeparator.equals(":") ? "////" : (File.pathSeparator.equals(";") ? "/" : "/////");
	private static Properties props = new Properties();
	private static CaigenConnectionPoolDataSource ccpd = null;
	private static final String dir = "";
	static {
		props.setProperty("delayedClose", "0");
		props.setProperty("charSet", "gbk");
		try {
			Class.forName("com.caigen.sql.dbf.DBFDriver");
			ccpd = new CaigenConnectionPoolDataSource();
			ccpd.setURL("jdbc:dbf:" + prex + dir + "?caseInsensitive=true");
			ccpd.setCharSet("gbk");
			ccpd.setDelayedClose(0);
			ccpd.setDateFormat("yyyy-MM-dd");
			ccpd.setTimestampFormat("yyyy-MM-dd hh:mm:ss");
			ccpd.setTimeFormat("hh:mm:ss");			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	private DBFUtil() {
	}
	private static DBFUtil inst=new DBFUtil();
	public static DBFUtil inst() {
		if(inst==null){
			inst=new DBFUtil();
		}
		return inst;
	}

	public Connection getConn() throws SQLException {
		return ccpd.getConnection();
	}

	public Connection getConn(String dir) {
		Connection conn = null;
		String jdbURL = "jdbc:dbf:" + prex + dir + "?caseInsensitive=true";
		try {
			conn = DriverManager.getConnection(jdbURL, props);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}

	/**
	 * 从dbf模板dbfFile中复制内容到其它dbf文件dbfFile2中
	 * 
	 * @param dbfFile
	 *            指定的模板
	 * @param dbfFile2
	 *            要复制的dbf文件
	 * @param row
	 *            要复制的行数
	 * @param start
	 *            从模板中第start列开始复制(负数表示从首列开始)
	 * @param length
	 *            从模板中第start列开始复制的列数（负数表示到最后一列）
	 */
	public void createDbfFromTemplate(File dbfFile, File dbfFile2, int row, int start, int length) {
		Connection con1 = getConn(dbfFile.getParent());
		Connection con2 = getConn(dbfFile2.getParent());
		String dbfTableName = dbfFile.getName();
		String dbfTableName2 = dbfFile2.getName();
		if (start < 0)
			start = 1;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();
			rs = con1.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY).executeQuery("select * from " + dbfTableName);
			ResultSetMetaData rm = rs.getMetaData();
			int count = rm.getColumnCount();
			if (null != rs) {
				rs.last();
				for (int i = start; i <= count && (length < 0 || i <= length); i++) {
					String type = rm.getColumnClassName(i);
					// System.out.println("type="+rs.getMetaData().
					// getColumnTypeName(i));
					if (type.equalsIgnoreCase("java.lang.String") || type.equals("java.sql.Date") || type.equals("java.sql.Timestamp")) {
						if (rm.getColumnName(i).equalsIgnoreCase("p3")) {
							sb.append("'TestP3'").append(",");
						} else {
							sb.append(rs.getObject(i) == null ? "null" : "'" + rs.getObject(i).toString() + "'").append(",");
						}
					} else {
						sb.append(rs.getObject(i) == null ? "null" : rs.getObject(i).toString()).append(",");
					}
				}
				if(start>1){
					sb.insert(0, "'TestP3','"+rs.getString("p22")+"',");
				}
			}
			rs.close();
			if (sb.length() > 0) {
				StringBuilder sql = new StringBuilder();
				sb.deleteCharAt(sb.length() - 1);
				for (int i = 1; i <= row; i++) {
					sql.append("(").append(sb.toString().replaceAll("TestP3", "" + i)).append("),");
					if (i % 20 == 0) {
						// System.out.println("sql="+sql.insert(0,
						// "insert into " + dbfTableName2 +
						// " values ").deleteCharAt(sql.length() -
						// 1).toString());
						con2.createStatement().executeUpdate(sql.insert(0, "insert into " + dbfTableName2 + " values ").deleteCharAt(sql.length() - 1).toString());
						sql = new StringBuilder();
						System.out.println(i + "条数据复制完成");
					}
				}
				if (sql.length() > 0) {
					con2.createStatement().executeUpdate(sql.insert(0, "insert into " + dbfTableName2 + " values ").deleteCharAt(sql.length() - 1).toString());
					System.out.println(row + "条数据复制完成");
				}
				System.out.println("OK");
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (con1 != null)
					con1.close();
				if (con2 != null)
					con2.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	/**
	 * 从dbf模板dbfFile中复制内容到其它dbf文件dbfFile2中
	 * 
	 * @param srcFile
	 *            指定的要从srcFile中复制的模板
	 * @param demoFile
	 *            要复制的空文件的模板
	 * @param dbfFile2
	 *            要复制的dbf文件模板
	 * @param row
	 *            要复制的行数
	 * @param start
	 *            从模板中第start列开始复制(负数表示从首列开始)
	 * @param length
	 *            从模板中第start列开始复制的列数（负数表示到最后一列）	 
	 * @param packageIndex 包的序号
	 * @throws IOException 
	 */
	public void createDbfFromTemplate(File srcFile, File demoFile,File dbfFile2, int row, int start, int length,int packageIndex) throws IOException {
		if(!demoFile.exists()){
			throw new RuntimeException("指定的源文件不存在");
			/*dbfFile2.getParentFile().mkdirs();
			try {
				dbfFile2.createNewFile();
			} catch (IOException e) {
				e.printStackTrace();
			}*/
		}
		FileUtils.renameTo(false,false, demoFile, dbfFile2);
		Connection con1 = getConn(srcFile.getParent());
		Connection con2 = getConn(dbfFile2.getParent());
		String dbfTableName = srcFile.getName();
		String dbfTableName2 = dbfFile2.getName();
		if (start < 0)
			start = 1;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();
			rs = con1.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY).executeQuery("select * from " + dbfTableName);
			ResultSetMetaData rm = rs.getMetaData();
			int count = rm.getColumnCount();
			if (null != rs) {
				rs.last();
				for (int i = start; i <= count && (length < 0 || i <= length); i++) {
					String type = rm.getColumnClassName(i);
					// System.out.println("type="+rs.getMetaData().
					// getColumnTypeName(i));
					if (type.equalsIgnoreCase("java.lang.String") || type.equals("java.sql.Date") || type.equals("java.sql.Timestamp")) {
						if (rm.getColumnName(i).equalsIgnoreCase("p3")) {
							sb.append("'TestP3'").append(",");
						} else {
							sb.append(rs.getObject(i) == null ? "null" : "'" + rs.getObject(i).toString() + "'").append(",");
						}
					} else {
						sb.append(rs.getObject(i) == null ? "null" : rs.getObject(i).toString()).append(",");
					}
				}
				if(start>1){
					sb.insert(0, "'TestP3','"+rs.getString("p22")+"',");
				}
			}
			rs.close();
			if (sb.length() > 0) {
				StringBuilder sql = new StringBuilder();
				sb.deleteCharAt(sb.length() - 1);
				for (int i = packageIndex*row+1; i <= row+packageIndex*row; i++) {
					sql.append("(").append(sb.toString().replaceAll("TestP3", "" + i)).append("),");
					if (i % 20 == 0) {
						// System.out.println("sql="+sql.insert(0,
						// "insert into " + dbfTableName2 +
						// " values ").deleteCharAt(sql.length() -
						// 1).toString());
						con2.createStatement().executeUpdate(sql.insert(0, "insert into " + dbfTableName2 + " values ").deleteCharAt(sql.length() - 1).toString());
						sql = new StringBuilder();
						System.out.println(i-(packageIndex*row+1) + "条数据复制完成");
					}
				}
				if (sql.length() > 0) {
					con2.createStatement().executeUpdate(sql.insert(0, "insert into " + dbfTableName2 + " values ").deleteCharAt(sql.length() - 1).toString());
					System.out.println(row + "条数据复制完成");
				}
				System.out.println("第"+packageIndex+"个包处理OK");
			}
			new ZipUtils().zipFile(dbfFile2.getParent()+"/"+dbfFile2.getName().replace(".dbf", ".zip"), new String[]{dbfFile2.getAbsolutePath()}, false);

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (con1 != null)
					con1.close();
				if (con2 != null)
					con2.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	/**
	 * 从dbf模板dbfFile中复制内容到其它dbf文件dbfFile2中(最大限制填满dbf文件)
	 * 
	 * @param dbfFile
	 *            指定的模板
	 * @param dbfFile2
	 *            要复制的dbf文件
	 * @param row
	 *            要复制的行数
	 * @param start
	 *            从模板中第start列开始复制(负数表示从首列开始)
	 * @param length
	 *            从模板中第start列开始复制的列数（负数表示到最后一列）
	 */
	public void createDbfFromTemplate(File dbfFile, File dbfFile2, int row, int start, int length,boolean full) {
		Connection con1 = getConn(dbfFile.getParent());
		Connection con2 = getConn(dbfFile2.getParent());
		String dbfTableName = dbfFile.getName();
		String dbfTableName2 = dbfFile2.getName();
		SimpleDateFormat date=new SimpleDateFormat("yyyy-MM-dd");
		SimpleDateFormat datetime=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		if (start < 0)
			start = 1;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();
			rs = con1.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY).executeQuery("select * from " + dbfTableName);
			ResultSetMetaData rm = rs.getMetaData();
			int count = rm.getColumnCount();
			if (null != rs) {
				rs.last();
				for (int i = start; i <= count && (length < 0 || i <= length); i++) {
					String type = rm.getColumnClassName(i);
					int size=rm.getColumnDisplaySize(i);
					// System.out.println("type="+rs.getMetaData().
					// getColumnTypeName(i));
					if (type.equalsIgnoreCase("java.lang.String") || type.equals("java.sql.Date") || type.equals("java.sql.Timestamp")) {
						if (rm.getColumnName(i).equalsIgnoreCase("p3")) {
							sb.append("'TestP3'").append(",");
						} else {
							sb.append(rs.getObject(i) == null ? "null" : "'" + rs.getObject(i).toString() + "'").append(",");
							if(rs.getObject(i) == null || rs.getObject(i).toString().trim().equals("")){
								if(type.equalsIgnoreCase("java.lang.String")){
									sb.append("'");
									for(int max=1;max<=size;max++){
										sb.append("1");										
									}
									sb.append("',");
								}else if(type.equals("java.sql.Date")){
									sb.append("'").append(date.format(new Date())).append("',");
								}else if(type.equals("java.sql.Timestamp")){
									sb.append("'").append(datetime.format(new Date())).append("',");
								}
							}else{
								if(type.equalsIgnoreCase("java.lang.String")){
									int max=rs.getString(i).length();
									if(max<=size){
										sb.append("'").append(rs.getObject(i).toString());
										while ( max <= size ) {
											sb.append("1");
											max++; 
										}
										sb.append("',");
									}
								}
							}
						}
					} else {
						sb.append(rs.getObject(i) == null ? "null" : rs.getObject(i).toString()).append(",");
					}
				}
				if(start>1){
					sb.insert(0, "'"+rs.getString("p3")+"',");
				}
			}
			rs.close();
			if (sb.length() > 0) {
				StringBuilder sql = new StringBuilder();
				sb.deleteCharAt(sb.length() - 1);
				for (int i = 1; i <= row; i++) {
					sql.append("(").append(sb.toString().replaceAll("TestP3", "" + i)).append("),");
					if (i % 20 == 0) {
						// System.out.println("sql="+sql.insert(0,
						// "insert into " + dbfTableName2 +
						// " values ").deleteCharAt(sql.length() -
						// 1).toString());
						con2.createStatement().executeUpdate(sql.insert(0, "insert into " + dbfTableName2 + " values ").deleteCharAt(sql.length() - 1).toString());
						sql = new StringBuilder();
						System.out.println(i + "条数据复制完成");
					}
				}
				if (sql.length() > 0) {
					System.out.println("sql="+sql);
					con2.createStatement().executeUpdate(sql.insert(0, "insert into " + dbfTableName2 + " values ").deleteCharAt(sql.length() - 1).toString());
					System.out.println(row + "条数据复制完成");
				}
				System.out.println("OK");
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (con1 != null)
					con1.close();
				if (con2 != null)
					con2.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public void deleteDbfFromTemplate(File dbfFile, int row, String whereStr) {
		Connection con = getConn(dbfFile.getParent());
		String dbfTableName = dbfFile.getName();
		try {
			ResultSet rs = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE).executeQuery("select * from " + dbfTableName + " " + whereStr);
			for (int i = 0; i < row; i++) {
				if (rs != null && (rs.first() )) {
					rs.deleteRow();
					System.out.println("删除"+(i+1)+"条");
				}
			}
			if (row == 0) {
				while (rs != null && rs.next()) {
					rs.deleteRow();
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		System.out.println("删除完毕！");
	}

	public static void splitDBF(File dbfFile, int count1) throws IOException {
		try {
			// 不支持 ALTER TABLE doesn't been supported
			Connection con = new DBFUtil().getConn(dbfFile.getParent());
			String dbfTableName = dbfFile.getName();
			File part1 = new File(dbfFile.getParent() + File.separator + "part1.dbf");
			File part2 = new File(dbfFile.getParent() + File.separator + "part2.dbf");
			FileUtils.renameTo(false,false, dbfFile, part1);
			FileUtils.renameTo(false,false, dbfFile, part2);
			ResultSet rs = con.createStatement().executeQuery("select * from " + dbfTableName);
			ResultSetMetaData rsm = rs.getMetaData();
			int count = rsm.getColumnCount();
			StringBuilder sb1 = new StringBuilder("alter table " + part1.getName());
			StringBuilder sb2 = new StringBuilder("alter table " + part1.getName());
			for (int i = count1; i <= count; i++) {
				if (i <= count1)
					sb1.append(" drop column " + rsm.getColumnName(i) + ",");
				else {
					sb2.append(" drop column " + rsm.getColumnName(i) + ",");
				}
			}
			sb1.deleteCharAt(sb1.length() - 1);
			sb2.deleteCharAt(sb2.length() - 1);
			System.out.println(sb1);
			System.out.println(sb2);
			con.createStatement().executeUpdate(sb1.toString());
			con.createStatement().executeUpdate(sb2.toString());
			System.out.println("拆分完成！");
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	public static void testUrl(String path) {
		System.out.println("path=" + path);
		try {
			ResultSet rs = DBFUtil.inst().getConn().createStatement().executeQuery("select * from " + path);
			while (rs != null && rs.next()) {
				System.out.println(rs.getObject(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void testRows(int row ,int col) throws SQLException{
		Connection con=DBFUtil.inst().getConn();
		StringBuffer sql=new StringBuffer();
		for(int i=1;i<=row;i++){
			sql.append("('abc'),");
		}
		sql.insert(0, "insert into test values").deleteCharAt(sql.length()-1);
		con.createStatement().executeUpdate("drop table if exists test;");
		con.createStatement().executeUpdate("create table test(name varchar(20))");
		con.createStatement().executeUpdate(sql.toString());
		ResultSet rs =con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY).executeQuery("select * from test");
		if(rs!=null){
			rs.last();
			System.out.println("rs.getRow="+rs.getRow());
		}
		rs.close();
		con.createStatement().executeUpdate("drop table if exists test");
		
	}
	public static void main(String[] args) throws Exception {
		/*
		 * createDbfFromTemplate(new File("C:/tmp/dbf/part1.dbf"),1000);
		 * createDbfFromTemplate(new File("C:/tmp/dbf/part2.dbf"),1000);
		 */
		// createDbfFromTemplate(new File("C:/tmp/dbf/李清超06.dbf"), 5000);
		// splitDBF(new File("C:/tmp/dbf/empty.dbf"), 255);//功能不正常
		// createDbfFromTemplate(new
		// File("C:/Users/Administrator/Desktop/tmp/李清超143.dbf"), 50);s
		// deleteDbfFromTemplate(new
		// File("C:/Users/Administrator/Desktop/tmp/李清超143backup.dbf"), 30,"");
		// createDbfFromTemplate(new
		// File("C:/Users/Administrator/Desktop/tmp/李清超p301.dbf"),new
		// File("C:/Users/Administrator/Desktop/tmp/李清超p301.dbf"), 50);
		// createDbfFromTemplate(new
		// File("C:/Users/Administrator/Desktop/tmp/李清超06.dbf"),new File(
		// "C:/Users/Administrator/Desktop/tmp/dbf文件模板/合并的dbf文件/allData.dbf"),
		// 1);
		//File file1 = new File("C:/Users/Administrator/Desktop/tmp/dbf文件模板/合并的dbf文件/allData.dbf");
		//DBFUtil.inst().createDbfFromTemplate(file1,file2, 1,-1,-1,true);
		for(int i=1;i<=1;i++){
		File target = new File("D:/test/hqcm_tran_demo7月.dbf");
			File demoFile=new File("D:/test/demo/hqcm_tran_demo.dbf");
			File file2 = new File("D:/test/part/hqcm_tran_demo"+i+".dbf");
			DBFUtil.inst().createDbfFromTemplate(target,demoFile,file2, 100,-1,-1,i);
		}
		//DBFUtil.inst().deleteDbfFromTemplate(new File("D:/hqcm_tran_demo7月.dbf"),7000,"");
		/*for(int i=1;i<=1;i++){
			File target = new File("D:/test/hqcm_tran_demo7月.dbf");
			File demoFile1=new File("D:/test/demo/hqcm_tran_demo_part1.dbf");
			File demoFile2=new File("D:/test/demo/hqcm_tran_demo_part2.dbf");
			File file1 = new File("D:/test/part/hqcm_tran_demo_part1.dbf");
			File file2 = new File("D:/test/part/hqcm_tran_demo_part2.dbf");
			DBFUtil.inst().createDbfFromTemplate(target,demoFile1,file1, 6000,-1,254,i);
			DBFUtil.inst().createDbfFromTemplate(target,demoFile2,file2, 6000,255,-1,i);
		}*/
		//DBFUtil.inst().createDbfFromTemplate(file2,file2, 50,-1,-1);
		// DBFUtil.inst().createDbfFromTemplate(file1,part1, 50,-1,254);
		// DBFUtil.inst().createDbfFromTemplate(file1,part2, 50,255,-1);
		 		 
		/*String path = "\"\\25401\\2012-08-30 10_19_47_25401_zip\".hqcm_tran_demo";		
		DBFUtil.testUrl("\"\\"+path1+"\\"+path2+"\"."+dbfFile[0].getName().replaceFirst(".dbf", ""));
		DBFUtil.testUrl("\"\\\\"+path1+"\\\\"+path2+"\"."+dbfFile[0].getName().replaceFirst(".dbf", ""));
		DBFUtil.testUrl("\\"+path1+"\\"+path2+"\\"+dbfFile[0].getName().replaceFirst(".dbf", ""));
		DBFUtil.testUrl("////"+path1+"////"+path2+"////"+dbfFile[0].getName().replaceFirst(".dbf", ""));
		DBFUtil.testUrl("/"+path1+"/"+path2+"/"+dbfFile[0].getName().replaceFirst(".dbf", ""));
		DBFUtil.testUrl(""+path1+"/"+path2+"/"+dbfFile[0].getName().replaceFirst(".dbf", ""));
		DBFUtil.testUrl("\""+path1+"/"+path2+"\"."+dbfFile[0].getName().replaceFirst(".dbf", ""));
		DBFUtil.testUrl("\\\\"+path1+"\\\\"+path2+"\\\\"+dbfFile[0].getName().replaceFirst(".dbf", ""));*/
		//DBFUtil.inst().testRows(55000, 1);
		//SimpleDateFormat dfYMDHMS4FileName = new SimpleDateFormat("yyyy-MM-dd HH_mm_ss.SSSS");
		//System.out.println(dfYMDHMS4FileName.format(new Date()));
		/*Date d=new Date(111111111111l);
		System.out.println(new SimpleDateFormat("yyyy-MM-dd HH_mm_ss.SSSS").format(d));
		Pattern p=Pattern.compile("\\S*\\.0*$");
		String input="0.000";
		System.out.println(input+"="+p.matcher(input).matches()+","+input.matches(".\\.0$"));
		input="1.0";
		System.out.println(input+"="+p.matcher(input).matches()+","+input.matches(".\\.0$"));
		input="1.00";
		System.out.println(input+"="+p.matcher(input).matches()+","+input.matches(".\\.0$"));
		input="1.00";
		System.out.println(input+"="+p.matcher(input).matches()+","+input.matches(".\\.0$"));
		input="1.000";
		System.out.println(input+"="+p.matcher(input).matches()+","+input.matches(".\\.0$"));
		input="1.01";
		System.out.println(input+"="+p.matcher(input).matches()+","+input.matches(".\\.0$"));
		input="1.02";
		System.out.println(input+"="+p.matcher(input).matches()+","+input.matches(".\\.0$"));
		input="1000";
		System.out.println(input+"="+p.matcher(input).matches()+","+input.matches(".\\.0$"));
		
		System.out.println("111111111111.00".replaceFirst(".0$", "").length());*/
		System.out.println("|"+" sd 枯       ".trim()+"|");
		JSONObject obj=new JSONObject();
		obj.put("key", 1);
		System.out.println(obj.get("key").getClass());
		obj.accumulate("key", "a");
		System.out.println(obj.get("key").getClass());
		System.out.println("obj="+obj);
		System.out.println(Integer.MAX_VALUE);
		
		/*UnZipUtil u=new UnZipUtil("D:/2012-10-19 11_49_59.423520_682.zip","D:/2012-10-19 11_49_59.423520_682.zip/");
		u.UnZipIt(true);*/
	}

}
